package com.simon.samples.server;

import java.sql.DriverManager;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.simon.samples.shared.NoExiste;
import com.simon.samples.shared.YaExiste;
import com.simon.samples.shared.world.Departamento;

public class DataBaseConnection {
	private static Connection connection;
	public DataBaseConnection(){
	}
	public static Connection getConnection() throws Exception {
		String username = "gwt";
		String password = "gwt";
		//String thin = "jdbc:mysql://" + "localhost" + ":	" + "3306"+ "/" + "gwt";
		Connection con = null;

		Class.forName("com.mysql.jdbc.Driver").newInstance();
		con = (Connection) DriverManager.getConnection("jdbc:mysql:///SITG","SITG", "SITG");
		con.setAutoCommit(true);
		return con;
	}
	public static boolean testUserPassword(String username,String password) throws Exception {
		if(connection == null){
			connection = getConnection();
		}
		Statement statement = (Statement) connection.createStatement();
		ResultSet result = (ResultSet) statement.executeQuery("select username, password from users where username = '"+username+"' and password = '"+password+"'");
		
		if(result.next()){
			if(result.getString("username").equals(username) && result.getString("password").equals(password)){
				return true;
			}
		}
		return false;
	}
	public static Departamento buscarDepartamento(String nombreDepto) throws Exception {
		if(connection == null){
			connection = getConnection();
		}
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement("select cod, descripcion from departamento where descripcion = ?");
		statement.setString(1, nombreDepto);
		ResultSet result = (ResultSet) statement.executeQuery();
		
		if(result.next()){
			return new Departamento(result.getInt("cod"), result.getString("descripcion"));
		}
		throw new NoExiste("Departamento '"+nombreDepto+"' no existe");
	}
	public static void insertarDepartamento(String nombreDepto) throws Exception {
		if(connection == null){
			connection = getConnection();
		}
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement("insert into departamento(descripcion) values(?)");
		statement.setString(1, nombreDepto);
		int afectadas = statement.executeUpdate();
	}
}
